<?php
/**
 * @copyright Copyright (c) 2021 勾股工作室
 * @license https://opensource.org/licenses/GPL-3.0
 * @link https://www.gougucms.com
 */
declare (strict_types = 1);
namespace app\home\controller;

use app\api\BaseController;
use app\home\severs\BarcodeSever;
use http\Params;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Exception;
use think\facade\Db;

class api extends BaseController
{
    //首页公告
    public function get_note_list()
    {
        $list = Db::name('Note')
            ->field('a.id,a.title,a.create_time,c.title as cate_title')
            ->alias('a')
            ->join('note_cate c', 'a.cate_id = c.id')
            ->where(['a.status' => 1])
            ->order('a.end_time desc,a.sort desc,a.create_time desc')
            ->limit(8)
            ->select()->toArray();
        foreach ($list as $key => $val) {
            $list[$key]['create_time'] = date('Y-m-d H:i', $val['create_time']);
        }
        $res['data'] = $list;
        return table_assign(0, '', $res);
    }
	
	//首页知识列表
    public function get_article_list()
    {
		$prefix = get_config('database.connections.mysql.prefix');//判断是否安装了文章模块
		$exist = Db::query('show tables like "'.$prefix.'article"');
		$res['data'] = [];
		if($exist){
			$list = Db::name('Article')
				->field('a.id,a.title,a.create_time,a.read,c.title as cate_title')
				->alias('a')
				->join('article_cate c', 'a.cate_id = c.id')
				->where(['a.delete_time' => 0])
				->order('a.id desc')
				->limit(8)
				->select()->toArray();
			foreach ($list as $key => $val) {
				$list[$key]['create_time'] = date('Y-m-d H:i', $val['create_time']);
			}
			$res['data'] = $list;			
		}
		return table_assign(0, '', $res);
	}

	function isAuthProject($uid)
	{
		if($uid == 1){
			return 1;
		}
		$map = [];
		$map[] = ['name', '=', 'project_admin'];
		$map[] = ['', 'exp', Db::raw("FIND_IN_SET('{$uid}',uids)")];
		$count = Db::name('DataAuth')->where($map)->count();
		return $count;
	}
    //首页项目
    public function get_project_list()
    {
		$prefix = get_config('database.connections.mysql.prefix');//判断是否安装了项目模块
		$exist = Db::query('show tables like "'.$prefix.'project"');
		$res['data'] = [];
		if($exist){
			$project_ids = Db::name('ProjectUser')->where(['uid' => $this->uid, 'delete_time' => 0])->column('project_id');
			$where =[];
			$where[] = ['a.delete_time', '=', 0];
			if($this->isAuthProject($this->uid)==0){
				$where[] = ['a.id', 'in', $project_ids];
			}
			$list = Db::name('Project')
				->field('a.id,a.name,a.status,a.create_time,a.start_time,a.end_time,u.name as director_name')
				->alias('a')
				->join('Admin u', 'a.director_uid = u.id')
				->where($where)
				->order('a.id desc')
				->limit(8)
				->select()->toArray();
			foreach ($list as $key => &$val) {
				$val['create_time'] = date('Y-m-d H:i', $val['create_time']);
				if($val['end_time']>0){
					$val['plan_time'] = date('Y-m-d', $val['start_time']) . ' 至 ' . date('Y-m-d', $val['end_time']);
				}
				else{
					$val['plan_time'] = '-';
				}
				$val['status_name'] = \app\project\model\Project::$Status[(int) $val['status']];
			}
			$res['data'] = $list;
		}
        return table_assign(0, '', $res);
    }
	
    //首页任务
    public function get_task_list()
    {
		$prefix = get_config('database.connections.mysql.prefix');//判断是否安装了项目模块
		$exist = Db::query('show tables like "'.$prefix.'project_task"');
		$res['data'] = [];
		if($exist){
			$where = array();
			$whereOr = array();
			$map1 = [];
			$map2 = [];
			$map3 = [];
			$map1[] = ['admin_id', '=', $this->uid];
			$map2[] = ['director_uid', '=', $this->uid];
			$map3[] = ['', 'exp', Db::raw("FIND_IN_SET({$this->uid},assist_admin_ids)")];
			if($this->isAuthProject($this->uid)==0){
				$whereOr =[$map1,$map2,$map3];
			}
			$where[] = ['delete_time', '=', 0];
			$list = Db::name('ProjectTask')
				->where(function ($query) use ($whereOr) {
					if (!empty($whereOr))
						$query->whereOr($whereOr);
					})
				->where($where)
				->withoutField('content,md_content')
				->order('flow_status asc')
				->order('id desc')
				->limit(8)
				->select()->toArray();
				foreach ($list as $key => &$val) {
					$val['director_name'] = Db::name('Admin')->where(['id' => $val['director_uid']])->value('name');
					if($val['end_time']>0){
						$val['end_time'] = date('Y-m-d', $val['end_time']);
					}
					else{
						$val['end_time'] = '-';
					}
					$val['flow_name'] = \app\project\model\ProjectTask::$FlowStatus[(int) $val['flow_status']];
				}
			$res['data'] = $list;
		}
        return table_assign(0, '', $res);
    }
	
    //获取访问记录
    public function get_view_data()
    {
        $param = get_params();
        $first_time = time();
        $second_time = $first_time - 86400;
        $three_time = $first_time - 86400 * 365;
        $begin_first = strtotime(date('Y-m-d', $first_time) . " 00:00:00");
        $end_first = strtotime(date('Y-m-d', $first_time) . " 23:59:59");
        $begin_second = strtotime(date('Y-m-d', $second_time) . " 00:00:00");
        $end_second = strtotime(date('Y-m-d', $second_time) . " 23:59:59");
        $begin_three = strtotime(date('Y-m-d', $three_time) . " 00:00:00");
        $data_first = Db::name('AdminLog')->field('create_time')->whereBetween('create_time', "$begin_first,$end_first")->select();
        $data_second = Db::name('AdminLog')->field('create_time')->whereBetween('create_time', "$begin_second,$end_second")->select();
        $data_three = Db::name('AdminLog')->field('create_time')->whereBetween('create_time', "$begin_three,$end_first")->select();
        return to_assign(0, '', ['data_first' => hour_document($data_first), 'data_second' => hour_document($data_second), 'data_three' => date_document($data_three)]);
    }
	
	//获取员工活跃数据
    public function get_view_log()
    {		
        $times = strtotime("-30 day");
        $where = [];
        $where[] = ['uid','<>',1];
        $where[] = ['create_time', '>', $times];
        $list = Db::name('AdminLog')->field("id,uid")->where($where)->select();
        $logs = array();
        foreach ($list as $key => $value) {
            $uid = $value['uid'];
            if (empty($logs[$uid])) {
                $logs[$uid]['count'] = 1;
                $logs[$uid]['name'] = Db::name('Admin')->where('id',$uid)->value('name');
            } else {
                $logs[$uid]['count'] += 1;
            }
        }
        $counts = array_column($logs, 'count');
        array_multisort($counts, SORT_DESC, $logs);
        //攫取前10
        $data_logs = array_slice($logs, 0, 10);
        return to_assign(0, '', ['data_logs' => $data_logs]);
    }
//统计板块开始
    /**
     * @autho:xiangzi
     * @auth true
     * @Date:2025/7/23
     * 获取看板左侧累计数据
     */
    public function getSjLjData()
    {
        $ljxsje=Db::name('sale_contract')->where('type',1)
            ->where('status',4)->sum('countMoney');
        $ljcbje=Db::name('sale_contract')->where('type',2)
            ->where('status',4)->sum('countMoney');
        $ljmlr=round($ljxsje-$ljcbje,2);
        if ($ljxsje>0){
            $mlrl=round($ljmlr/$ljxsje,2);
        }else{
            $mlrl=0;
        }


        $ljhkje=Db::name('money_order_contract_detail')->where('type',1)
            ->where('status',2)->sum('money');
        $ljwsje=round($ljxsje-$ljhkje,2);

        $saleNum=Db::name('sale_contract_detail')->where('status',4)
            ->where('type',1)->sum('bjNum');
        $buNum=Db::name('stock_order_detail')->where('type',3)
            ->where('status',2)->sum('num');
        $ddhwsl=round($saleNum-$buNum,2);

        $data=[
            'ljxsje'=>$ljxsje,
            'ljcbje'=>$ljcbje,
            'ljmlr'=>$ljmlr,
            'mlrl'=>$mlrl,
            'ljhkje'=>$ljhkje,
            'ljwsje'=>$ljwsje,
            'ddhwsl'=>$ddhwsl,
        ];
        return to_assign(0,'操作成功',$data);
    }



    /**
     * @autho:xiangzi
     * @auth true
     * @Date:2025/7/23
     * 按年份获取年累计数据
     */
    public function getSjLjBuyYear()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $yearArr=[$year."-01-01",$year."-12-31"];
        $bnxsje=Db::name('sale_contract')->where('type',1)
            ->where('orderDate',"between",$yearArr)
            ->where('status',4)->sum('countMoney');
       $bncbje= Db::name('sale_contract')->where('type',2)
           ->where('orderDate',"between",$yearArr)
            ->where('status',4)->sum('countMoney');
       $bnmlr=round($bnxsje-$bncbje,2);
       $bnfye=Db::name('expense_interfix')->alias('ei')
           ->leftJoin('expense e','e.id=ei.exid')
           ->where('e.check_status',5)
           ->where('e.delete_time',0)
           ->whereYear('e.pay_time',$year)->sum('ei.amount');

       $data=[
           'bnxsje'=>$bnxsje,
           'bncbje'=>$bncbje,
           'bnmlr'=>$bnmlr,
           'bnfye'=>$bnfye,
       ];
        return to_assign(0,'操作成功',$data);
    }

    /**
     * @autho:xiangzi
     * @auth true
     * @Date:2025/7/23
     * 看板金额统计
     */
    public function getjeytj()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $yearArr=[$year."-01-01",$year."-12-31"];
        $hkList=Db::name('money_order')
            ->where('orderDate',"between",$yearArr)
            ->where('type',1)
            ->where('status',2)
            ->field('orderDate,money')
            ->select();

        $fkList=Db::name('money_order')
            ->where('orderDate',"between",$yearArr)
            ->where('type',3)
            ->where('status',2)
            ->field('orderDate,money')
            ->select();;
        $hkData=[];
        $fkData=[];
        foreach ($hkList as $hl){
            $times=strtotime($hl['orderDate']);
            $time=sprintf('%d',date('m',$times));
            if (isset($hkData[$time])){
                $hkData[$time]+=$hl['money'];
            }else{
                $hkData[$time]=$hl['money'];
            }
        }
        foreach ($fkList as $hl){
            $times=strtotime($hl['orderDate']);
            $time=sprintf('%d',date('m',$times));
            if (isset($fkData[$time])){
                $fkData[$time]+=$hl['money'];
            }else{
                $fkData[$time]=$hl['money'];
            }
        }
        $mon=[];
        $hkje=[];
        $cgje=[];
        for($i=1;$i<=12;$i++){
            $mon[]=$i;
            $hkje[]=$hkData[$i]??0;
            $cgje[]=$fkData[$i]??0;
        }

        $data=[
            'mon'=>$mon,
            'hkje'=>$hkje,
            'cgje'=>$cgje
        ];
        return to_assign(0,'操作成功',$data);
}
    public function gethtytj()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $yearArr=[$year."-01-01",$year."-12-31"];
        $lastYear=$year-1;
        $lastYearArr=[$lastYear."-01-01",$lastYear."-12-31"];
        $thisList=Db::name('sale_contract')->where('type',1)
            ->where('status',4)->where('orderDate',"between",$yearArr)
            ->field('orderDate,countMoney')->select()->each(function ($item){
                $item['time']=sprintf('%d',date('m',strtotime($item['orderDate'])));
                return $item;
            });
        $lastList=Db::name('sale_contract')->where('type',1)
            ->where('status',4)->where('orderDate',"between",$lastYearArr)
            ->field('orderDate,countMoney')->select()->each(function ($item){
                $item['time']=sprintf('%d',date('m',strtotime($item['orderDate'])));
                return $item;
            });
        $thisData=[];
        foreach ($thisList as $v){
            $t=$v['time'];
            if (isset($thisData[$t])){
                $thisData[$t]+=$v['countMoney'];
            }else{
                $thisData[$t]=$v['countMoney'];
            }
        }
        $lastData=[];
        foreach ($lastList as $v){
            $t=$v['time'];
            if (isset($lastData[$t])){
                $lastData[$t]+=$v['countMoney'];
            }else{
                $lastData[$t]=$v['countMoney'];
            }
        }

        $mon=[];
        $thisYearNum=[];
        $lastYearNum=[];
        for($i=1;$i<=12;$i++){
            $mon[]=$i;
           if (isset($thisData[$i])){
               $thisYearNum[]=$thisData[$i];
           }else{
               $thisYearNum[]=0;
           }
            if (isset($lastData[$i])){
                $lastYearNum[]=$lastData[$i];
            }else{
                $lastYearNum[]=0;
            }
        }
        $data=[
            'mon'=>$mon,
            'thisYear'=>$thisYearNum,
            'lastYear'=>$lastYearNum
        ];
        return to_assign(0,'操作成功',$data);
    }
    public function getddytjt()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $lastYear=$year-1;
        $tbNum=[];
        $hbNum=[];
        $thisYearNum=[];
        $lastYearNum=[];
        for($i=1;$i<=12;$i++){
            $mon[]=$i;
            $thisNum=Db::name('sale_contract')->where('type',1)
                ->where('status',4)->whereMonth('orderDate',$year.'-'.$i)
                ->count();
            $thisYearNum[]=$thisNum;
            $lasNum=Db::name('sale_contract')->where('type',1)
                ->where('status',4)->whereMonth('orderDate',"$lastYear".'-'.$i)
                ->count();
            $lastYearNum[]=$lasNum;
            if ($lasNum==0){
                $tbNum[]=0;
            }else{
                $tbNum=round(($thisNum-$lasNum)/$lasNum,2);
            }
        }
        foreach ($thisYearNum as $key=>$v){
            if ($key==0){
                $lasMont=$lastYearNum[11]??0;
            }else{
                $lasMont=$thisYearNum[$key-1];
            }
            if ($lasMont==0){
                $hbNum[]=0;
            }else{
                $hbNum[]=round(($v-$lasMont)/$lasMont,2);
            }
        }
        $data=[
            'mon'=>$mon,
            'tbNum'=>$tbNum,
            'hbNum'=>$hbNum,
        ];
        return to_assign(0,'操作成功',$data);
    }

    public function getmlrytjt()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $yearArr=[$year."-01-01",$year."-12-31"];
        $thisSale=Db::name('sale_contract')->where('type',1)
            ->where('status',4)->where('orderDate',"between",$yearArr)
            ->field('orderDate,countMoney')->select()->each(function ($item){
                $item['time']=sprintf('%d',date('m',strtotime($item['orderDate'])));
                return $item;
            });
        $thisCb=Db::name('sale_contract')->where('type',2)
            ->where('status',4)->where('orderDate',"between",$yearArr)
            ->field('orderDate,countMoney')->select()->each(function ($item){
                $item['time']=sprintf('%d',date('m',strtotime($item['orderDate'])));
                return $item;
            });
        $monthSal=[];
        foreach ($thisSale as $v){
            $t=$v['time'];
            if (isset($monthSal[$t])){
                $monthSal[$t]+=$v['countMoney'];
            }else{
                $monthSal[$t]=$v['countMoney'];
            }
        }
        $monthCb=[];
        foreach ($thisCb as $v1){
            $t=$v1['time'];
            if (isset($monthCb[$t])){
                $monthCb[$t]+=$v1['countMoney'];
            }else{
                $monthCb[$t]=$v1['countMoney'];
            }
        }

        $mon=[];
        $thisYearNum=[];

        for($i=1;$i<=12;$i++){
            $mon[]=$i;
            $sale=$monthSal[$i]??0;
            $cb=$monthCb[$i]??0;
            $thisYearNum[]=round($sale-$cb,2);

        }
        $data=[
            'mon'=>$mon,
            'thisYear'=>$thisYearNum,
        ];
        return to_assign(0,'操作成功',$data);
}

    public function getytdlrl()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $yearArr=[$year."-01-01",$year."-12-31"];
        $xsje=Db::name('sale_contract')->where('type',1)
            ->where('status',4)->where('orderDate',"between",$yearArr)->sum('countMoney');
        $cbje=Db::name('sale_contract')->where('type',2)
            ->where('status',4)->where('orderDate',"between",$yearArr)->sum('countMoney');

        if ($xsje!=0){
            $lrl=round(($xsje-$cbje)/$xsje,2);
        }else{
            $lrl=0;

        }

        return to_assign(0,'操作成功',['lrl'=>round($lrl*100,2)]);
}

    public function getytdwcl()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $ndMb=Db::name('sale_target')->where('status',2)
            ->where('years',$year)->sum('targets');
        $salOutList=Db::name('stock_order_detail')
            ->alias('sod')
            ->leftJoin('stock_order so','so.id=sod.orderId')
            ->leftJoin('sale_contract_detail scd','scd.id=sod.saleContractDetailId')
            ->where('sod.status',2)
            ->where('sod.type',1)
            ->where('so.orderDate','between',[$year.'-01-01',$year.'-12-31'])
            ->field('sod.num,scd.bjPrice')->select();
        $salRefundList=Db::name('stock_order_detail')
            ->alias('sod')
            ->leftJoin('stock_order so','so.id=sod.orderId')
            ->leftJoin('sale_contract_detail scd','scd.id=sod.saleContractDetailId')
            ->where('sod.status',2)
            ->where('sod.type',2)
            ->where('so.orderDate','between',[$year.'-01-01',$year.'-12-31'])
            ->field('sod.num,scd.bjPrice')->select();
        $mon=sprintf('%d',date('m'));
        if ($year!=date('Y')){
            $mon=12;
        }
        $ouMoney=0;
        $refundMoney=0;
        foreach ($salOutList as $sol){
            $money=$sol['num']*$sol['bjPrice'];
            $ouMoney+=$money;
        }
        foreach ($salRefundList as $srl){
            $money=$srl['num']*$srl['bjPrice'];
            $refundMoney+=$money;
        }
        if ($ndMb!=0){
            $wcl=round((($ouMoney-$refundMoney)/10000)/(($ndMb/12)*$mon),2);
            $wwc=$wcl>=1?0:1-$wcl;
        }else{
            $wcl=0;
            $wwc=0;
        }

        return to_assign(0,'操作成功',['wcl'=>round($wcl*100,2),'wwc'=>$wwc]);
}

    public function getqnwcl()
    {
        $param=$this->request->param();
        $year=$param['year']??date('Y');
        $ndMb=Db::name('sale_target')->where('status',2)
            ->where('years',$year)->sum('targets');
        $salOutList=Db::name('stock_order_detail')
            ->alias('sod')
            ->leftJoin('stock_order so','so.id=sod.orderId')
            ->leftJoin('sale_contract_detail scd','scd.id=sod.saleContractDetailId')
            ->where('sod.status',2)
            ->where('sod.type',1)
            ->where('so.orderDate','between',[$year.'-01-01',$year.'-12-31'])
            ->field('sod.num,scd.bjPrice')->select();
        $salRefundList=Db::name('stock_order_detail')
            ->alias('sod')
            ->leftJoin('stock_order so','so.id=sod.orderId')
            ->leftJoin('sale_contract_detail scd','scd.id=sod.saleContractDetailId')
            ->where('sod.status',2)
            ->where('sod.type',2)
            ->where('so.orderDate','between',[$year.'-01-01',$year.'-12-31'])
            ->field('sod.num,scd.bjPrice')->select();

        $ouMoney=0;
        $refundMoney=0;
        foreach ($salOutList as $sol){
            $money=$sol['num']*$sol['bjPrice'];
            $ouMoney+=$money;
        }
        foreach ($salRefundList as $srl){
            $money=$srl['num']*$srl['bjPrice'];
            $refundMoney+=$money;
        }
        if ($ndMb!=0){
        $wcl=round((($ouMoney-$refundMoney)/10000)/$ndMb,2);
        $wwc=$wcl>=1?0:1-$wcl;
    }else{
        $wcl=0;
        $wwc=0;
        }
        return to_assign(0,'操作成功',['wcl'=>round($wcl*100,2),'wwc'=>$wwc]);
}

    //统计板块结束

    /**
     * @return \json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * date:2024/3/21
     * 获取数状物料
     */
    public function getMaterial_type(){
        $rule = Db::name('material_type')->where(['status'=>1])->order('id asc')->field('*,name as title')->select()->toArray();
        return to_assign(0,'获取成功',generateTree($rule)) ;
    }
    public function getPrintType(){
        $list=getPrintType();
        return to_assign(0,'获取成功',$list) ;
    }

    /**
     * date:2024/4/17
     * 获取仓库详情
     */
    public function getWarHoseDetail()
    {
        $param=request()->param();
        $id=$param['id']??'';
        if (!$id){
            return to_assign(1,'参数缺失') ;
        }
        $where[]=['warehouse_id','in',$id];
        $where[]=['status','=',1];
        $list=Db::name('warehouse_detail')->where($where)->field('id,name')->select();
        return to_assign(0,'获取成功',$list) ;
    }

    /**
     * @return \json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * date:2024/4/25
     * 工序申报物料
     */
    public function getProductionPlanDetail()
    {
        $param=request()->param();
        $id=$param['id']??'';
        if (!$id){
            return to_assign(1,'参数缺失') ;
        }
        $where[]=['ppd.status','=',1];
        $where[]=['ppd.production_plan_id','=',$id];
        $list=Db::name('production_plan_detail')->alias('ppd')
            ->join('material_dossier md','md.id=ppd.material_dossier_id','left')
            ->field('ppd.material_dossier_id,md.name,md.specs,md.bar_code,md.unit,ppd.craft_route_id')
            ->where($where)
            ->select();
        return to_assign(0,'获取成功',$list) ;
    }

    /**
     * date:2024/4/25
     * 工序
     */
    public function getGx()
    {
        $param=request()->param();
        $mid=$param['mid']??'';
        $djid=$param['danjuid']??'';
        $route_id=$param['route_id']??'';
        if (!$mid||!$djid||!$route_id){
            return to_assign(1,'参数缺失') ;
        }
        $where[]=['cr.status','=',1];
        $where[]=['crd.status','=',1];
        $where[]=['crd.craft_route_id','=',$route_id];
        $where[]=['crd.material_dossier_id','=',$mid];
        $list=Db::name('craft_route_detail')->alias('crd')
            ->join('craft_route cr','cr.id=crd.craft_route_id','left')
            ->where($where)
            ->field("crd.id,(select name from fy_craft c where c.id=crd.caraf_id) craftName")
            ->select();
        $mdInfo=Db::name('material_dossier')->where('id',$mid)->field('name,unit,specs')->find();
        $res=['mdInfo'=>$mdInfo,'list'=>$list];
        return to_assign(0,'获取成功',$res) ;
    }

    /**
     * date:2024/4/25
     *工序申报班组/个人id
     */
    public function getPricingMode()
    {
        $param=request()->param();
        $type=$param['type']??'';
        if (!$type){
            return to_assign(1,'参数缺失') ;
        }
        if ($type=='个人'){
            $list=Db::name('admin')->where('status',1)
                ->field('id,name as name')->select();
        }else{
            $list=Db::name('team')->where('status',1)
                ->field('id,name')->select();
        }
        return to_assign(0,'获取成功',$list) ;
    }

    public function getBomDetail()
    {
        $param=$this->request->param();
        $bomIds=$param['bomIds']??'';
        $nums=$param['nums']??'';
        if (!$bomIds||!$nums){
            return to_assign(1,'参数缺失');
        }
        $bomIds=explode(',',$bomIds);
        $nums=explode(',',$nums);
        $data=[];
        foreach ($bomIds as $key=> $v){
            $num1=$nums[$key]??0;
            $where=[];
            $where[]=['bd.deleted','=',0];
            $where[]=['pid','=',$v];
            $list=Db::name('bom_detail')->alias('bd')
                ->leftJoin('material_dossier md1','md1.id=bd.material_dossier_id')
                ->where($where)
                ->leftJoin('material_kucun_view mkv','mkv.mdId=bd.material_dossier_id and mkv.wId=md1.warehouse_id')
                ->field("bd.num,bd.loss_rate,bd.id,mkv.mdId,mkv.bar_code,mkv.mdName,mkv.specs,mkv.unit,mkv.countNum,mkv.sendNum,mkv.wId")
                ->select();
            foreach ($list as $l){
                $l['countNum']=$l['countNum']??0;
                $l['sendNum']=$l['sendNum']??0;
                $kcNum=$l['countNum']-$l['sendNum'];
                $yjNum=round($num1*$l['num']*(1+$l['loss_rate']),2);
                $l['kcNum']=$kcNum;
                $l['yjNum']=$yjNum;
                $data[]=$l;
            }
        }
        $res=[];
        $zhData=[];
        foreach ($data as $d){
            $mdId=$d['mdId'];
            if (isset($res[$mdId])){
                $res[$mdId]+=$d['yjNum'];
            }else{
                $res[$mdId]=$d['yjNum'];
            }
            if (!isset($zhData[$mdId])){
                $zhData[$mdId]=$d;
            }
        }
        $result=[];
        foreach ($zhData as$k=>$zd){
            $zd['yjNum']=$res[$k]??$zd['yjNum'];
            $result[]=$zd;
        }
        return to_assign(0,'获取成功',$result);
    }


    public function getKhCard()
    {
        $param =$this->request->param();
        $time=$param['time']??'';
        $times=[];
        $times1=[];
        if ($time){
            $times=explode('~',$time);
            $times1=[strtotime($times[0]),$times[1]." 23:59:59"];
        }
        $khId=$param['khId']??'';

        $sql1=Db::name('sale_hanging_bill')->where('h_type',2)->where('status','>',-1);
        if ($times1){
            $sql1->where('create_time','between',$times1);
        }
        if ($khId){
            $sql1->where('punter_id','=',$khId);
        }
        $xsje=$sql1->sum('countPrice');
        $sql2=Db::name('sale_reconcile')->where('s_type',2)->where('status','=',2);
//            ->where('is_end',1);
        if ($times){
            $sql2->where('reconcileDate','between',$times);
        }
        if ($khId){
            $sql2->where('punter_id','=',$khId);
        }
        $dzje=$sql2->sum('reconcilePrice');
        $sql3=Db::name('receipt_order_detail')->where('status',2)
            ->where('rod_type',2);
        if ($times){
            $s_time=strtotime($times[0]);
            $e_time=strtotime($times[1].' 23:59:59');
            $sql3->where('create_time','between',[$s_time,$e_time]);
        }
        if ($khId){
            $sql3->where('punter_id',$khId);
        }
        $yhje=$sql3 ->sum('money');
        $whje=$xsje-$yhje;
        return to_assign(0,'ok',['xsje'=>round($xsje/10000,2),
            'dzje'=>round($dzje/10000,2),
            'yhje'=>round($yhje/10000,2),
            'whje'=>round($whje/10000,2)
        ]);

    }

    public function getKhLine()
    {
        $param =$this->request->param();
        $time=$param['lineTime']??'';
        $type=$param['lineType']??'';
        $khId=$param['lineName']??'';
        $sql1=Db::name('sale_hanging_bill')->where('h_type',2)->where('status','>',-1);
        $times=[];
        if ($time){
            $times=explode(' ~ ',$time);
        }
        if ($times&&$type==1){
            $s_time=strtotime($times[0].'-01-01 00:00:00');
            $e_time=strtotime($times[1]."-12-31 23:59:59");
            $sql1->whereTime('create_time','between',[$s_time,$e_time]);
        }
        if ($times&&$type==2){
            $s_time=strtotime($times[0].'-01 00:00:00');
            $e_time=strtotime(date('Y-m-t',strtotime($times[1])).' 23:59:59');
            $sql1->whereTime('create_time','between',[$s_time,$e_time]);
        }
        if ($khId){
            $sql1->where('punter_id','=',$khId);
        }
        if ($type==1){
            if (!$times){
                $thisYear=strtotime(date('Y-12-t').' 23:59:59');
                $berorYear=strtotime(date('Y-m-01',strtotime("-3 years")));
                $sql1->whereTime('create_time','between',[$berorYear,$thisYear]);
            }
            $sql1->field("DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y') as rDate,countPrice as reconcilePrice");
        }else{
            if (!$times){
                $thisDate=strtotime(date('Y-m-t')." 23:59:59");
                $beforDate=strtotime(date('Y-m-01',strtotime('-35 months')));
                $sql1->whereTime('create_time','between',[$beforDate,$thisDate]);
            }
          $sql1->field("DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m') as rDate,countPrice as reconcilePrice");
        }
        $list=$sql1->select();
        $data=[];
        foreach ($list as $v){
            if (isset($data[$v['rDate']])){
                $data[$v['rDate']]+=$v['reconcilePrice'];
            }else{
                $data[$v['rDate']]=$v['reconcilePrice'];
            }
        }
        $date=[];
        $line=[];
        foreach ($data as $d=>$dd){
            $date[]=$d;
            $line[]=round($dd/10000,2);
        }
        return to_assign(0,'ok',['time'=>$date,'data'=>$line]);

    }

    public function getGysCard()
    {
        $param =$this->request->param();
        $time=$param['time']??'';
        $times=[];
        $times1=[];
        if ($time){
            $times=explode('~',$time);
            $times1=[strtotime($times[0]),$times[1]." 23:59:59"];
        }
        $khId=$param['khId']??'';

//        $cgje=0;
//        $cgdz=0;
//        $yfje=0;
//        $wfje=0;
        $sql1=Db::name('sale_hanging_bill')->where('h_type',1)
            ->where('status','>',-1);
        if ($times1){
            $sql1->where('create_time','between',$times1);
        }
        if ($khId){
            $sql1->where('punter_id','=',$khId);
        }
        $cgje=$sql1->sum('countPrice');
        $sql2=Db::name('sale_reconcile')
            ->where('s_type',1)->where('status','=',2);
//            ->where('is_end',1);
        if ($times){
            $sql2->where('reconcileDate','between',$times);
        }
        if ($khId){
            $sql2->where('punter_id','=',$khId);
        }
        $cgdz=$sql2->sum('reconcilePrice');
        $sql3=Db::name('receipt_order_detail')->where('status',2)
            ->where('rod_type',1);
        if ($times){
            $s_time=strtotime($times[0]);
            $e_time=strtotime($times[1].' 23:59:59');
            $sql3->where('create_time','between',[$s_time,$e_time]);
        }
        if ($khId){
            $sql3->where('punter_id',$khId);
        }
        $yfje=$sql3 ->sum('money');
        $wfje=$cgje-$yfje;
        return to_assign(0,'ok',[
            'cgje'=>round($cgje/10000,2),
            'cgdz'=>round($cgdz/10000,2),
            'yfje'=>round($yfje/10000,2),
            'wfje'=>round($wfje/10000,2)
        ]);
    }

    public function getGysLine()
    {
        $param =$this->request->param();
        $time=$param['lineTime']??'';
        $type=$param['lineType']??'';
        $khId=$param['lineName']??'';

        $sql1=Db::name('sale_hanging_bill')->where('h_type',1)
            ->where('status','>',-1);
        $times=[];
        if ($time){
            $times=explode(' ~ ',$time);
        }
        if ($times&&$type==1){
            $s_time=strtotime($times[0].'-01-01 00:00:00');
            $e_time=strtotime($times[1]."-12-31 23:59:59");
            $sql1->whereTime('create_time','between',[$s_time,$e_time]);
        }
        if ($times&&$type==2){
            $s_time=strtotime($times[0].'-01 00:00:00');
            $e_time=strtotime(date('Y-m-t',strtotime($times[1])).' 23:59:59');
            $sql1->whereTime('create_time','between',[$s_time,$e_time]);
        }
        if ($khId){
            $sql1->where('punter_id','=',$khId);
        }
        if ($type==1){
            if (!$times){
                $thisYear=strtotime(date('Y-12-t').' 23:59:59');
                $berorYear=strtotime(date('Y-m-01',strtotime("-3 years")));
                $sql1->whereTime('create_time','between',[$berorYear,$thisYear]);
            }
            $sql1->field("DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y') as rDate,countPrice as reconcilePrice");
        }else{
            if (!$times){
                $thisDate=strtotime(date('Y-m-t')." 23:59:59");
                $beforDate=strtotime(date('Y-m-01',strtotime('-35 months')));
                $sql1->whereTime('create_time','between',[$beforDate,$thisDate]);
            }
            $sql1->field("DATE_FORMAT(FROM_UNIXTIME(create_time), '%Y-%m') as rDate,countPrice as reconcilePrice");
        }
        $list=$sql1->select();
        $data=[];
        foreach ($list as $v){
            if (isset($data[$v['rDate']])){
                $data[$v['rDate']]+=$v['reconcilePrice'];
            }else{
                $data[$v['rDate']]=$v['reconcilePrice'];
            }
        }
        $date=[];
        $line=[];
        foreach ($data as $d=>$dd){
            $date[]=$d;
            $line[]=round($dd/10000,2);
        }
        return to_assign(0,'ok',['time'=>$date,'data'=>$line]);
    }

    public function getBarcodeImg($barcode)
    {
        return BarcodeSever::Brcode($barcode);
    }

    public function getFtMx()
    {
        $param=$this->request->param();
        $date=$param['shareDate']??'';
        if (!$date){
            return to_assign(1,'参数缺失') ;
        }
        $dates=explode(" ~ ",$date);
        $where[]=['status','=',3];
        $where[]=['orderDate','between',$dates];
        $list=Db::name('process_report_view')->where($where)
            ->group('craft_detail_id')
            ->field("craft_detail_id ,sum(qualifiedNum) as num,craftName")->select();
        return to_assign(0,'获取成功',$list) ;
    }

    /**
     * @return \json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @autho:xiangzi
     * @auth:true
     * @Date:2024/10/28
     * 获取库存
     */
    public function getMdKuCun()
    {
       $param=$this->request->param();
        $mdId=$param['mdId']??'';
        $wId=$param['wId']??'';
        if (!$wId||!$mdId){
            return to_assign(1,'参数缺失',[]);
        }
        $info=Db::name('material_kucun_view')->where('mdId',$mdId)->where('wId',$wId)->find();
        if ($info){
            $info['kcNum']=$info['countNum']-$info['sendNum'];
        }else{
            $info['kcNum']=0;
        }
        return to_assign(0,'操作成功',$info);

    }

    /**
     * @autho:xiangzi
     * @auth:true
     * @Date:2024/12/6
     * 获取供应商/客户联系人、收货人
     */
    public function getPurveyorMember()
    {
        $param=$this->request->param();
        $purveyorId=$param['purveyorId']??'';
        if (!$purveyorId){
            return to_assign(1,'参数缺失');
        }
        $contactList=Db::name('purveyor_member')
            ->where('status','=',1)
            ->where('purveyorId','=',$purveyorId)
            ->where('type',1)->field('id,name,phone')->select();
        $saverList=Db::name('purveyor_member')
            ->where('status','=',1)
            ->where('purveyorId','=',$purveyorId)
            ->where('type',2)->field('id,name,phone,address,addType')->select();

        return to_assign(0,'获取成功',['contactList'=>$contactList,'saverList'=>$saverList]);
    }

    /**
     * @return \json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @autho:xiangzi
     * @auth:true
     * @Date:2024/12/6
     * 部门用户
     */
    public function getDepartUser()
    {
        $param=$this->request->param();
        $did=$param['did']??'';
        if (!$did){
            return to_assign(1,'参数缺失');
        }
        $adList=Db::name('admin')
            ->where('status',1)
            ->where('did',$did)
            ->field('id,name')->select();

        return to_assign(0,'获取成功',$adList);
    }

    /**
     * @return \json
     * @throws \think\db\exception\DbException
     * @autho:xiangzi
     * @auth:true
     * @Date:2024/12/10
     * 获取库存明细
     */
    public function getCkMx()
    {
        $table='stock_mx_view';
        $param=$this->request->param();
        $wId=$param['wId']??'';
        $times=$param['times']??'';
        if ($wId){
            $where[]=['wId','=',$wId];
        }
        if ($times){
            $t1=explode('~',$times);
            $where[]=['checkTime','between',$t1];
        }
        $mdId=$param['mdId']??0;
        $detailId=$param['detailId']??0;
        $where[]=['status','=',2];
        $where[]=['type','in',[2,3]];
        $where[]=['mdId','=',$mdId];
        $limit=$param['limit']??15;
        $list=Db::name($table)
            ->where($where)
            ->field('*')
            ->order('id asc')
            ->paginate($limit)->each(function ($item) use ($detailId){
                $checkTime=strtotime($item['checkTime']);
                $item['outNum']=$item['outNum']??0;
                $item['lockNum']=$item['lockNum']??0;
                $item['canUse']=$item['num']-$item['outNum']-$item['lockNum'];
                $item['dayLong']=ceil((time()-$checkTime)/86400);
                $item['checkId']=0;
                $item['thisLock']='';
                $lockInfo=Db::name('sale_contract_lock')
                    ->where('status','>',-1)
                    ->where('detailId',$detailId)
                    ->where('stockOrderDetailId',$item['id'])->field('id,stockOrderDetailId,lockNum')->find();

                if ($lockInfo){
                    $item['checkId']=$lockInfo['stockOrderDetailId'];
                    $item['thisLock']=$lockInfo['lockNum'];
                    $item['canUse']+=$lockInfo['lockNum'];
                }
                return $item;
        });
        return table_assign(0,'获取成功',$list);
    }

    /**
     * @autho:xiangzi
     * @auth:true
     * @Date:2025/4/16
     * 销售明细的锁定页面专用接口
     */
//    public function getSaleMxLockData()
//    {
//        $param=$this->request->param();
//        $mdId=$param['mdId']??0;
//        $detailId=$param['detailId']??0;
////        $detailInfo=Db::name('sale_contract_detail')->where('id',$detailId)->find();
//        $limit=$param['limit']??15;
//        $where[]=['scd.status','=',4];
//        $where[]=['scd.type','=',2];
//        $where[]=['scd.saleContractDetailId','=',$detailId];
//        $list =Db::name('sale_contract_detail')
//            ->alias('scd')
//            ->leftJoin('sale_contract sc','sc.id=scd.orderId')
//            ->leftJoin('purveyor p','p.id=sc.punterId')
//            ->where($where)
//            ->field('scd.*,sc.orderNum,p.name as pName')
//            ->paginate($limit)->each(function ($item)use ($detailId){
//                $item['inNum']=Db::name('stock_order_detail')
//                    ->where('status',2)
//                    ->where('type','3')
//                    ->where('saleContractDetailId',$item['id'])->sum('num');
//                $unInNum=$item['bjNum']-$item['inNum'];
//                $item['unInNum']=$unInNum<0?0:$unInNum;
//                $countIn=Db::name('stock_order_detail')
//                    ->where('type','in',[2,3])
//                    ->where('status',2)
//                    ->where('saleContractDetailId','in',[$detailId,$item['id']])->sum('num');
//                $countOut=Db::name('stock_order_detail')
//                    ->where('type','in',[1,4])
//                    ->where('status',2)
//                    ->where('saleContractDetailId','in',[$detailId,$item['id']])->sum('num');
//                $item['zkNum']=$countIn-$countOut;
//                $lockedNum=Db::name('sale_contract_lock')
//                    ->where('status','>',-1)
//                    ->where('detailId',$detailId)
//                    ->where('buyContactMxId',$item['id'])
//                    ->sum('lockNum');
//                $item['lockedNum']=$lockedNum;
//                $item['canUse']=$countIn-$countOut-$lockedNum+$unInNum;
//                $item['countIn']=$countIn;
//                $item['countOut']=$countOut;
////                $lockInfo=Db::name('sale_contract_lock')
////                    ->where('status','>',-1)
////                    ->where('detailId',$detailId)
////                    ->where('buyContactMxId',$item['id'])->field('id,buyContactMxId,lockNum')->find();
////
////                if ($lockInfo){
////                    $item['checkId']=$lockInfo['buyContactMxId'];
////                    $item['thisLock']=$lockInfo['lockNum'];
////                    $item['canUse']+=$lockInfo['lockNum'];
////                    $item['lockedId']=$lockInfo['id'];
////                }
//                return $item;
//            });
//
//        return table_assign(0,'获取成功',$list);
//    }
    public function getSaleMxLockData()
    {
        $param=$this->request->param();
        $mdId=$param['mdId']??0;
        $detailId=$param['detailId']??0;
        $limit=$param['limit']??15;
        $where[]=['sod.status','=',2];
//        $where[]=['sod.sodType','=',1];
        $where[]=['sod.sodType','in',[1,5]];
        $where[]=['sod.mdId','=',$mdId];
        if (isset($param['pName'])&&$param['pName']){
            $where[]=['p.name','like','%'.$param['pName']."%"];
        }
        if (isset($param['orderNum'])&&$param['orderNum']){
            $where[]=['sc.orderNum','like','%'.$param['orderNum']."%"];
        }
        $list=Db::name('stock_order_detail')
            ->alias('sod')
            ->leftJoin('stock_order so','so.id=sod.orderId')
            ->leftJoin('sale_contract_detail scd','scd.id=sod.saleContractDetailId')
            ->leftJoin('sale_contract sc','sc.id=scd.orderId')
            ->leftJoin('purveyor p','p.id=sc.punterId')
            ->where($where)
            ->field('sod.*,p.name as pName,so.orderNum as rukuOrder,so.checkTime,scd.bjPrice,scd.orderId as cghtId')
            ->paginate($limit)->each(function ($item){
                $inTime=strtotime($item['checkTime']);
                $dayLong= ceil((time()-$inTime)/86400);
                $item['dayLong']=$dayLong;
                $locked=Db::name('sale_contract_lock')
                    ->where('stockOrderDetailId',$item['id'])->where('status','>',-1)
                    ->sum('lockNum');
                $item['lockedNum']=$locked;
                $canUse=$item['num']-$locked;
                $item['canUse']=$canUse;
                return $item;
            });
//halt($list);
        return table_assign(0,'获取成功',$list);
    }
    public function geKcMxInfo()
    {
        $param=$this->request->param();
        $saleContractDetailId=$param['saleContractDetailId']??'';
        $wId=$param['wId']??'';
        $mdId=$param['mdId']??'';
        if (!$saleContractDetailId||!$mdId){
            return to_assign(1,'参数缺失');
        }

        $whear1[]=['saleContractDetailId','=',$saleContractDetailId];
        $whear1[]=['status','=',4];
        $sIds=Db::name('sale_contract_detail')
            ->where($whear1)->column('id');
        $where[]=['saleContractDetailId','in',$sIds];
//        $where[]=['wId','=',$wId];
        $where[]=['mdId','=',$mdId];
        $info=Db::name('stock_mx_view')
            ->where($where)
            ->order('create_time asc')
            ->limit(1)
            ->find();
        if(!$info){
            return  to_assign(1,'暂无数据');
        }
        return to_assign(0,'获取成功',$info);

    }

    public function getKHXSY()
    {
       $param=$this->request->param();
       $id=$param['purveyorId']??0;
       if (!$id){
           return to_assign(1,'参数缺失');
       }
       $uids=Db::name('purveyor')
           ->where('id',$id)->value('saleIds');
       $list=Db::name('admin')->where('id','in',$uids)->field('id,name')->select();
       return to_assign(0,'操作成功',$list);

    }

    public function getSalContractMxImportData()
    {

        try {
            if (!isset($_FILES['file'])){
                throw new Exception('请选择上传文件');
            }
            //限制上传表格类型
            $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
            //application/vnd.ms-excel  为xls文件类型
            if ($fileExtendName != 'xlsx') {
                throw new Exception('必须为excel表格，且必须为xlsx格式！');
            }

            if (is_uploaded_file($_FILES['file']['tmp_name'])) {
                // 有Xls和Xlsx格式两种
//            $objReader = \IOFactory::createReader('Xlsx');
                $objReader = IOFactory::createReader('Xlsx');

                $filename = $_FILES['file']['tmp_name'];

                $objPHPExcel = $objReader->setReadDataOnly(true)->load($filename);  //$filename可以是上传的表格，或者是指定的表格

                $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                $highestRow = $sheet->getHighestRow();       // 取得总行数
                $importData = [];

                //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
                for ($j = 2; $j <= $highestRow; $j++) {
                    $specs=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getFormattedValue();
                    if ($specs){


                    $bjNum=(float)$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getFormattedValue();
                    $bjPrice=(float)$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getFormattedValue();
                    $disAmount=$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getFormattedValue();
                    $bjTime=$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getFormattedValue();
                    $khlh=$objPHPExcel->getActiveSheet()->getCell("F" . $j)->getFormattedValue();
                    $proCode=$objPHPExcel->getActiveSheet()->getCell("G" . $j)->getFormattedValue();
                    $remark=$objPHPExcel->getActiveSheet()->getCell("H" . $j)->getFormattedValue();
                    $mdInfo=Db::name('material_dossier')->where('status',1)
                        ->where('specs',$specs)->find();
                    if (!$mdInfo){
                        throw new Exception($j ."行产品 {$specs}不存在");
                    }
                    if (!$bjNum){
                        throw new Exception($j ."行产品 {$specs}数量有误");
                    }
                    $mdInfo['bjNum']=$bjNum;
                    $mdInfo['bjPrice']=$bjPrice;
                    $mdInfo['bjTime']=$bjTime;
                    $mdInfo['khlh']=$khlh;
                    $mdInfo['proCode']=$proCode;
                    $mdInfo['disAmount']=$disAmount;
                    $mdInfo['remark']=$remark;
                    $importData[]=$mdInfo;
                }
                }

            }

        }catch (Exception $e){
            return to_assign(1,'操作失败:'.$e->getMessage());
        }
        return to_assign(0,'导入成功',$importData);

    }
    public function getBuyContractMxImportData()
    {

        try {
            if (!isset($_FILES['file'])){
                throw new Exception('请选择上传文件');
            }
            //限制上传表格类型
            $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);
            //application/vnd.ms-excel  为xls文件类型
            if ($fileExtendName != 'xlsx') {
                throw new Exception('必须为excel表格，且必须为xlsx格式！');
            }

            if (is_uploaded_file($_FILES['file']['tmp_name'])) {
                // 有Xls和Xlsx格式两种
//            $objReader = \IOFactory::createReader('Xlsx');
                $objReader = IOFactory::createReader('Xlsx');

                $filename = $_FILES['file']['tmp_name'];

                $objPHPExcel = $objReader->setReadDataOnly(true)->load($filename);  //$filename可以是上传的表格，或者是指定的表格

                $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
                $highestRow = $sheet->getHighestRow();       // 取得总行数
                $importData = [];
//                'specs' => '型号',
//            'num' => '数量',
//            'price' => '含税单价',
//            'remark' => '备注',
//            'bjTime' => '货期',
                //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
                for ($j = 2; $j <= $highestRow; $j++) {
                    $specs=$objPHPExcel->getActiveSheet()->getCell("A" . $j)->getFormattedValue();
                    if ($specs){


                    $bjNum=(float)$objPHPExcel->getActiveSheet()->getCell("B" . $j)->getFormattedValue();
                    $bjPrice=(float)$objPHPExcel->getActiveSheet()->getCell("C" . $j)->getFormattedValue();
                    $remark=$objPHPExcel->getActiveSheet()->getCell("D" . $j)->getFormattedValue();
                    $bjTime=$objPHPExcel->getActiveSheet()->getCell("E" . $j)->getFormattedValue();

                        $mdInfo=Db::name('material_dossier')
                            ->field("*,name as mdName,id as mdId")
                            ->where('status',1)
                            ->where('specs',$specs)
                            ->find();
//                            ->each(function ($item){
////                    $saleNum=Db::name('stock_order_detail')
////                        ->where('status',2)
////                        ->where('type',1)
////                        ->where('mdId',$item['mdId'])->sum('num');
//                                $item['saleNum']=0;
//                                $item['unBuyNum']=0;
//                                $item['saleContractOrderNum']='';
//                                $item['salePrice']=0;
//                                $lastBuyMoney=Db::name('sale_contract_detail')
//                                    ->where('type',2)
//                                    ->where('mdId',$item['id'])
//                                    ->where('status',4)
//                                    ->order('create_time desc')
//                                    ->limit(1)
//                                    ->value('bjPrice');
//                                $item['lasPrice']=$lastBuyMoney??'0';
//                                return $item;
//                            });
//                    $mdInfo=Db::name('material_dossier')->where('status',1)
//                        ->where('specs',$specs)->find();
                    if (!$mdInfo){
                        throw new Exception($j ."行产品 {$specs}不存在");
                    }
                    if (!$bjNum||!$bjPrice){
                        throw new Exception($j ."行产品 {$specs}数量或金额有误");
                    }
                    $mdInfo['bjNum']=$bjNum;
                    $mdInfo['bjPrice']=$bjPrice;
                    $mdInfo['bjTime']=$bjTime;
                    $mdInfo['remark']=$remark;
                    $mdInfo['saleNum']=0;
                    $mdInfo['unBuyNum']=0;
                    $mdInfo['saleContractOrderNum']='';
                    $mdInfo['salePrice']=0;
                    $lastBuyMoney=Db::name('sale_contract_detail')
                    ->where('type',2)
                    ->where('mdId',$mdInfo['id'])
                    ->where('status',4)
                    ->order('create_time desc')
                    ->limit(1)
                    ->value('bjPrice');
                    $mdInfo['lasPrice']=$lastBuyMoney??'0';
                    $importData[]=$mdInfo;

                }
                }

            }

        }catch (Exception $e){
            return to_assign(1,'操作失败:'.$e->getMessage());
        }
        return to_assign(0,'导入成功',$importData);

    }

}
